import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'mock_data.dart';

class DatabaseHelper {
  //使用单例模式
  DatabaseHelper._init();
  static final DatabaseHelper instance = DatabaseHelper._init();
  static Database? _database;

  Future<Database> get database async {
    if (_database != null) return _database!;

    _database = await _initDB('steam_replica.db');
    return _database!;
  }

  //初始化数据库：获取路径并打开连接。
  Future<Database> _initDB(String fileName) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, fileName);


    return await openDatabase(
      path,
      version: 1,
      onCreate: _createDB,
      onConfigure: _onConfigure,
    );
  }

  //启用外键
  Future<void> _onConfigure(Database db) async {

    await db.execute('PRAGMA foreign_keys = ON');
  }

  Future<void> _createDB(Database db, int version) async {
    const createUserTable = '''
    CREATE TABLE users (
        id TEXT PRIMARY KEY,
        account TEXT UNIQUE NOT NULL,
        name TEXT,
        password TEXT,
        avatar TEXT,
        balance REAL
    );
    ''';

    const createGameTable = '''
    CREATE TABLE games (
        id TEXT PRIMARY KEY,
        name TEXT,
        description TEXT,
        coverImage TEXT,
        developer TEXT
    );
    ''';

    const createProductTable = '''
    CREATE TABLE products (
        id TEXT PRIMARY KEY,
        name TEXT,
        description TEXT,
        type TEXT,
        price REAL,
        gameId TEXT,
        FOREIGN KEY (gameId) REFERENCES games (id) ON DELETE CASCADE
    );
    ''';

    const createUserProductTable = '''
    CREATE TABLE user_products (
        id TEXT PRIMARY KEY,
        userId TEXT,
        productId TEXT,
        purchaseDate TEXT,
        FOREIGN KEY (userId) REFERENCES users (id) ON DELETE CASCADE,
        FOREIGN KEY (productId) REFERENCES products (id) ON DELETE CASCADE
    );
    ''';

    const createWishlistItemsTable = '''
    CREATE TABLE wishlist_items (
        userId TEXT,
        gameId TEXT,
        PRIMARY KEY (userId, gameId),
        FOREIGN KEY (userId) REFERENCES users (id) ON DELETE CASCADE,
        FOREIGN KEY (gameId) REFERENCES games (id) ON DELETE CASCADE
    );
    ''';


    await db.execute(createUserTable);
    await db.execute(createGameTable);
    await db.execute(createProductTable);
    await db.execute(createUserProductTable);
    await db.execute(createWishlistItemsTable);

    //测试数据
    await _seedData(db);
  }

  //插入测试数据
  Future<void> _seedData(Database db) async {
    //使用Batch模式提高批量插入的性能
    final batch = db.batch();

    //插入用户
    for (final user in MockData.users) {
      batch.insert('users', user.toMap());
    }

    //插入游戏
    for (final game in MockData.games) {
      batch.insert('games', game.toMap());
    }

    //插入产品
    for (final product in MockData.products) {
      batch.insert('products', product.toMap());
    }

    //插入用户拥有的产品记录
    for (final userProduct in MockData.userProducts) {
      batch.insert('user_products', userProduct.toMap());
    }

    //插入愿望单记录
    for (final item in MockData.wishlistItems) {
      batch.insert('wishlist_items', item);
    }

    //提交所有操作
    await batch.commit(noResult: true);
    print("Mock data seeded successfully!");
  }

  //关闭数据库连接。
  Future<void> close() async {
    final db = await instance.database;
    db.close();
  }
}